#!/usr/bin/python
# -*- coding: UTF-8 -*-


import os
import datetime
from os import truncate
from openpyxl import load_workbook
from openpyxl.styles import Font
from win32com.client import Dispatch


redFont = Font(u'宋体', size=10, bold=False, italic=False,
               strike=False, color='ff0000')


MODIFY_LAKE_VALUE_MAP = {
    '玉溪市生活垃圾焚烧发电项目': 'A9:AI9',
    '易门县污水处理厂配套管网工程': 'A10:AI10'
}

SRC_FOLDER = 'src'



def log(*val):
    print(f'[Auto Process] {" ".join(val)}')


def read_from_xlsx(path, offset=0):
    '''从 xlsx 文件读取数据'''
    wb = load_workbook(path, data_only=True)
    ws = wb.active
    data = list(ws.values)[offset:]
    res = []
    for row in data:
        res.append(row)
    return res

# 用openpyxl读取excel的load_workbook加上data_only参数，读取不到值的原因。
# 要用excel打开并保存才会读得到
# https://blog.51cto.com/antidarkness/1974684
#


def openAndSave(filename):
    xlApp = Dispatch("KET.Application")
    xlApp.Visible = False
    path = os.path.join(os.getcwd(), filename)
    xlBook = xlApp.Workbooks.Open(path)
    xlBook.Save()
    xlBook.Close()


class Report():
    def __init__(self):
        self.set_folder()

    def set_folder(self):
        folder = input('输入月份，格式为 <2020-03>：')
        [year, month] = folder.split('-')
        this_month = datetime.date(int(year), int(month), 1)
        last_month = datetime.date(int(year), int(month) - 1, 1)
        self.this_month = f'{this_month.year}年{this_month.month}月'
        self.last_month = f'{last_month.year}年{last_month.month}月'
        # log(self.last_month, '->', self.this_month)

        log(self.last_month, '->', self.this_month)

        self.folder = f'{SRC_FOLDER}/{folder}'
        self.files = os.listdir(f'{SRC_FOLDER}/{folder}')


class ZihuanchuReport(Report):
    def __init__(self):
        super().__init__()
        report_name = '云南省中央预算内投资和专项建设基金资环专项项目执行情况'
        self.src_file = f'玉溪市-{self.last_month}-{report_name}.xlsx'
        self.dst_file = f'玉溪市-{self.this_month}-{report_name}.xlsx'

    def generate(self):
        # 如果上月报表不存在，则用模板报表
        src_file = self.src_file if os.path.exists(
            self.src_file) else 'zihuanchu_report.xlsx'
        wb = load_workbook(src_file)
        ws = wb.active

        for f in self.files:
            data = read_from_xlsx(f'{self.folder}/{f}', offset=7)
            # 获取数据来源标识
            tableKey = str(data[0][0]).replace('\n', '')
            if not tableKey in MODIFY_LAKE_VALUE_MAP:
                log(f'文件 {f} {tableKey} 不在要处理的范围内')
                continue

            log('处理', tableKey)

            # 获取修改的区域
            modify_area = ws[MODIFY_LAKE_VALUE_MAP.get(tableKey)]
            offset = 0
            isUpdate = False
            for rowIndex, row in enumerate(modify_area):
                if row[0].value == data[rowIndex][0]:
                    for colIndex in range(5, 35):
                        if row[colIndex].value != data[rowIndex][colIndex + offset]:
                            log(
                                f'修改 {row[colIndex].coordinate}, {row[colIndex].value} -> {data[rowIndex][colIndex + offset]}')
                            row[colIndex].value = data[rowIndex][colIndex + offset]
                            row[colIndex].font = redFont
                            isUpdate = True
            if isUpdate:
                log(f'{tableKey} 有更新')
            else:
                log(f'{tableKey} 无更新')

        ws['A2'].value = f'云南省中央预算内投资和专项建设基金资环专项项目执行情况{self.this_month}报表'
        ws['G3'].value = f'填报日期：{datetime.date.today()}'
        ws['O4'].value = f'截至{self.this_month}底到位投资'
        ws['U4'].value = f'截至{self.this_month}底完成投资'
        wb.save(self.dst_file)
        log('完成')


if __name__ == "__main__":
    lake_report = ZihuanchuReport()
    lake_report.generate()
